package com.assignment.database;

/**
 * 	Database Connection Class, provides static methods to perform MySql general database operations 
 * 	that are not project specific.
 *  
 * 
 * @author John Wright and Luke Williams
 * @version 1.0
 *                                                                                                                                                                                                                                                                                                                                                                                                    
 */

import java.sql.Connection;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public abstract class DatabaseHandler {

	// Attributes
	private static String username, password, dbPath;
	static Connection connection;

	/**
	 * Method to initialise the database class, preparing it for use.
	 * This method must be called in order to access the database
	 * @param user	The username required to access the database
	 * @param pass	The password required to access the database
	 * @param path	The database file path/location. For a local database this would be "localhost/databasename"
	 */
	public static void initialise(String user, String pass, String path) {

		username = user;
		password = pass;
		dbPath = path;

	}

	
	/**
	 * Performs the connection operation to the database, using the values specified in the initialise()
	 * method. Must be called prior to executing any SQL queries.
	 * @return	Whether connection is successful
	 */
	public static boolean connect() {

		try {
			connection = DriverManager.getConnection("jdbc:mysql://" + dbPath,
					username, password);

			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}

	}

	
	/**
	 * Performs a commit on the database, saving the data. Database is set to auto-commit
	 * by default, so this method is usually unnecessary.
	 * @return Whether successful
	 */
	public static boolean commit() {

		try {
			connection.commit();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	
	/**
	 * Performs a roll back operation on the database, going back to the previous commit.
	 * @return Whether successful
	 */
	public static boolean rollback() {

		try {
			connection.rollback();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}

	
	/**
	 *  Closes the connection to the database. Should be used after every database query / set of
	 *  queries, and then connect() used to reopen the connection when needed.
	 * @return Whether successful
	 */
	public static boolean close() {

		try {
			connection.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	/**
	 * Exectues an SQL statement where nothing is returned, or the user has no interest
	 * in the return data. Should be used for insert,update, and delete operation.
	 * 
	 * @param query The SQL statement to be executed
	 * @return Whether successful
	 */
	public static boolean doSQL(String query) {

		Statement stmt;

		try {
			stmt = connection.createStatement();
			stmt.executeUpdate(query);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}

	}

	
	/**
	 * Executes an SQL query and returns the resulting table
	 *  as a 2D String array.
	 * @param query The SQL query to be executed
	 * @return The query result
	 */
	public static String[][] returnQuery(String query) {

		StringBuilder output = new StringBuilder();
		Statement stmt;

		try {
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			int rowCount = 0;

			while (rs.next()) {
				for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
					output.append(rs.getString(i) + "~");
				}
				output.append("\n");
				rowCount++;
			}

			String[][] result = new String[rs.getMetaData().getColumnCount()][rowCount];

			String s[] = output.toString().split("~");

			int i = 0;
			for (int y = 0; y < result[0].length; y++) {
				for (int x = 0; x < result.length; x++) {

					result[x][y] = s[i];
					i++;
				}
			}

			return result;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

	}

	
	/**
	 * Executes an SQL query and returns the resulting table
	 *  as a 2D String array, ignoring image files.
	 * @param query The SQL query to be executed
	 * @return The query result
	 */
	public static String[][] returnQueryNoImage(String query) {

		StringBuilder output = new StringBuilder();
		Statement stmt;

		try {
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			int rowCount = 0;

			while (rs.next()) {
				for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
					//if (i == 9) {
					if (rs.getString(i)==null) {
						output.append(" ~");
					}
					else if (rs.getString(i).length()>250) {
						output.append(" ~");
					} else {
						output.append(rs.getString(i) + "~");
					}
				}
				output.append("\n");
				rowCount++;
			}

			String[][] result = new String[rs.getMetaData().getColumnCount()][rowCount];

			String s[] = output.toString().split("~");

			int i = 0;
			for (int y = 0; y < result[0].length; y++) {
				for (int x = 0; x < result.length; x++) {

					result[x][y] = s[i];
					i++;
				}
			}

			return result;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

	}

	/**
	 * Takes a 2D String array and prints the variable to the console
	 * formatted as a table. Useful for printing query results from the 
	 * returnQuery() method for debugging
	 * @param query The query to be printed
	 */
	public static void printQuery(String[][] query) {

		for (int y = 0; y < query[0].length; y++) {
			for (int x = 0; x < query.length; x++) {

				System.out.print(query[x][y] + "\t");
			}
			System.out.println();
		}

	}

	

}
